-- SELECT * FROM FN_LOAD_LOTS_FOR_NEW_TEST_RELEASES(  1254, 2, 2, 'FT', 'PROD' ) ORDER BY PS_TYPE_ID DESC, ISNULL( EXPECTED_DATE_OUT, GETDATE())
ALTER FUNCTION FN_LOAD_LOTS_FOR_NEW_TEST_RELEASES
(
	@RELEASE_PROCESSSTEP_ID INT,
	@FROM_VENDOR_ID			INT,
	@TO_VENDOR_ID 			INT,
	@RELEASE_PS_TYPE		VARCHAR(50),
	@RELEASE_TYPE			VARCHAR(50)
)
RETURNS @RESULTS TABLE( ROW_ID INT IDENTITY, BASEPRODUCT_NAME VARCHAR(50), FROM_PART_NUMBER VARCHAR(50), TO_PART_NUMBER VARCHAR(50), FROM_PROCESSSTEP_ID INT, TO_PROCESSSTEP_ID INT, FROM_VENDOR_ID VARCHAR(50), FROM_VENDOR_NAME VARCHAR(50), TO_VENDOR_ID VARCHAR(50), TO_VENDOR_NAME VARCHAR(50), PS_TYPE VARCHAR(50), PS_TYPE_ID INT, LOT_ID INT, LOT_NUMBER VARCHAR(50),FAB_LOT_NUMBER VARCHAR(50), QTY_AVAILABLE INT, DATE_IN DATETIME, EXPECTED_DATE_OUT DATETIME, IS_HOLD INT, IS_ENG INT, IS_RMA INT, QTY_PULLED INT )
AS
BEGIN
	DECLARE @LOT_CURSOR 			CURSOR
	DECLARE @PRODUCT_CURSOR			CURSOR
	DECLARE @LOT_NUMBER 			VARCHAR(50)
	DECLARE @ROW_ID 				INT
	DECLARE @QTY_AVAILABLE 			INT
	DECLARE @QTY_PULLED 			INT
	DECLARE @BASE_PRODUCT_NAME		VARCHAR(50)
	DECLARE @SOURCE_PART_NUMBER		VARCHAR(50)
	DECLARE @RELEASE_PART_NUMBER	VARCHAR(50)
	DECLARE @RELEASE_VENDOR_NAME	VARCHAR(50)
	DECLARE @IS_HOLD 				INT
	DECLARE @IS_ENG 				INT
	DECLARE @IS_RMA 				INT
	DECLARE @FROM_LOCATION_ID 		INT
	DECLARE @SOURCE_PROCESSSTEP_ID 	INT
	DECLARE @PROCESSSTEP_ID 		INT
	DECLARE @CURRENT_PROCESS_EXP_DATE_OUT	VARCHAR(50)
	DECLARE @PS_TYPE_ID				VARCHAR(50)
	DECLARE @FROM_VENDOR_NAME		VARCHAR(50)
	DECLARE @DIE_QUANTITY			INT
	DECLARE @LOT_ID					INT
	
	SELECT @FROM_LOCATION_ID = LOCATION_ID, @FROM_VENDOR_NAME = V_NAME FROM LOCATION L, VENDOR V WHERE L.VENDOR_ID = V.VENDOR_ID AND V.VENDOR_ID = @FROM_VENDOR_ID AND L_TYPE = 'ASSEMBLY'
	DECLARE @PROGRAMMABLE_PARTS_TABLE TABLE ( ProcessStep_ID INT )

	DECLARE @Programmable_Parts_Cursor CURSOR
	SET @Programmable_Parts_Cursor = CURSOR FOR SELECT ProcessStep_ID FROM FN_GET_PROCESS_FLOW_BACKWARD_BY_ID( @RELEASE_PROCESSSTEP_ID ) WHERE PS_TYPE = 'ASSEMBLY'
	OPEN @Programmable_Parts_Cursor
	FETCH NEXT FROM @Programmable_Parts_Cursor INTO @SOURCE_PROCESSSTEP_ID
	WHILE @@FETCH_STATUS = 0 
	BEGIN
		INSERT INTO @PROGRAMMABLE_PARTS_TABLE SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID( @SOURCE_PROCESSSTEP_ID, 1 ) WHERE PS_TYPE = 'FG'
		FETCH NEXT FROM @Programmable_Parts_Cursor INTO @SOURCE_PROCESSSTEP_ID
	END
	CLOSE @Programmable_Parts_Cursor
	DEALLOCATE @Programmable_Parts_Cursor
	
	SELECT @RELEASE_PART_NUMBER = PART_NUMBER FROM PRODUCT WHERE PROCESSSTEP_ID = @RELEASE_PROCESSSTEP_ID
	
	SELECT TOP 1 @BASE_PRODUCT_NAME = P1.PART_NUMBER FROM PRODUCT P1, PRODUCT P2 WHERE P1.PRODUCT_ID = P2.P_PARENT_PRODUCT_ID AND P2.PROCESSSTEP_ID = @RELEASE_PROCESSSTEP_ID

	SELECT TOP 1 @RELEASE_VENDOR_NAME = V_NAME FROM VENDOR WHERE VENDOR_ID = @TO_VENDOR_ID

	INSERT INTO @RESULTS ( BASEPRODUCT_NAME, FROM_PART_NUMBER, TO_PART_NUMBER, FROM_PROCESSSTEP_ID, TO_PROCESSSTEP_ID, FROM_VENDOR_ID, FROM_VENDOR_NAME, TO_VENDOR_ID, TO_VENDOR_NAME, PS_TYPE, PS_TYPE_ID, LOT_ID, LOT_NUMBER, FAB_LOT_NUMBER, QTY_AVAILABLE, DATE_IN, EXPECTED_DATE_OUT, QTY_PULLED ) 
		SELECT (select TOP 1 P.Part_number from assm_release_lot_Table arlt, Lot_Table LTT, Product p where LTT.Lot_Id = arlt.Lot_ID and LTT.ProcessStep_id = P.ProcessStep_ID and ARLT.Shipped_lot_number = ( CASE WHEN LEN(LT.Lot_number) > 5 THEN SUBSTRING( Lt.Lot_number, 0, 6 ) ELSE LT.Lot_Number END )), P.Part_Number, @RELEASE_PART_NUMBER, Lt.ProcessStep_ID, @RELEASE_PROCESSSTEP_ID, @FROM_VENDOR_ID, @FROM_VENDOR_NAME, @TO_VENDOR_ID, @RELEASE_VENDOR_NAME, PS.PS_Type, 1, Lt.Lot_ID, Lt.Lot_Number, Lt.Fab_Lot_number, Lt.Qty_In, 
			( SELECT EXPECTED_DATE_OUT FROM ASSEMBLY_LOT WHERE ASSEMBLY_LOT.LOT_ID = LT.LOT_ID ), ( SELECT EXPECTED_DATE_OUT FROM ASSEMBLY_LOT WHERE ASSEMBLY_LOT.LOT_ID = LT.LOT_ID ), (SELECT SUM( ISNULL( QTY_PULLED, 0 ) ) FROM TEST_RELEASE_LOT_TABLE TRLT, TEST_RELEASE_TABLE TRT WHERE TRLT.TEST_REL_ID = TRT.TEST_REL_ID AND TEST_REL_STATUS <> 'CANCELLED' AND TRLT.LOT_ID = LT.LOT_ID )
		FROM Lot_Table LT, PRODUCT P, PROCESS_STEP PS, LOCATION L WHERE Lt.ProcessStep_ID = P.ProcessStep_ID AND Lt.ProcessStep_ID = PS.ProcessStep_ID AND Lt.Location_ID = L.Location_ID 
		AND LT.LOT_CURRENT_STATUS <> 'Close' AND Lt.ProcessStep_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID( @RELEASE_PROCESSSTEP_ID, 0 ) WHERE PS_TYPE = 'ASSEMBLY' ) 
		AND L.Location_ID = @FROM_LOCATION_ID

	INSERT INTO @RESULTS ( BASEPRODUCT_NAME, FROM_PART_NUMBER, TO_PART_NUMBER, FROM_PROCESSSTEP_ID, TO_PROCESSSTEP_ID, FROM_VENDOR_ID, FROM_VENDOR_NAME, TO_VENDOR_ID, TO_VENDOR_NAME, PS_TYPE, PS_TYPE_ID, LOT_ID, LOT_NUMBER,FAB_LOT_NUMBER, QTY_AVAILABLE, DATE_IN, EXPECTED_DATE_OUT, QTY_PULLED ) 
		SELECT (select TOP 1 P.Part_number from assm_release_lot_Table arlt, Lot_Table LTT, Product p where LTT.Lot_Id = arlt.Lot_ID and LTT.ProcessStep_id = P.ProcessStep_ID and ARLT.Shipped_lot_number = ( CASE WHEN LEN(LT.Lot_number) > 5 THEN SUBSTRING( Lt.Lot_number, 0, 6 ) ELSE LT.Lot_Number END )), P.Part_Number, @RELEASE_PART_NUMBER, Lt.ProcessStep_ID, @RELEASE_PROCESSSTEP_ID, @FROM_VENDOR_ID, @FROM_VENDOR_NAME, @TO_VENDOR_ID, @RELEASE_VENDOR_NAME, PS.PS_Type, 1, Lt.Lot_ID, Lt.Lot_Number, Lt.Fab_Lot_number, Lt.Qty_In, 
			Lt.Date_In, Lt.Date_In, (SELECT SUM( ISNULL( QTY_PULLED, 0 ) ) FROM TEST_RELEASE_LOT_TABLE TRLT, TEST_RELEASE_TABLE TRT WHERE TRLT.TEST_REL_ID = TRT.TEST_REL_ID AND TEST_REL_STATUS <> 'CANCELLED' AND TRLT.LOT_ID = LT.LOT_ID ) 
		FROM Lot_Table LT, PRODUCT P, PROCESS_STEP PS, LOCATION L WHERE Lt.ProcessStep_ID = P.ProcessStep_ID AND Lt.ProcessStep_ID = PS.ProcessStep_ID AND Lt.Location_ID = L.Location_ID 
		AND LT.LOT_CURRENT_STATUS <> 'Close' AND Lt.ProcessStep_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID( @RELEASE_PROCESSSTEP_ID, 0 ) WHERE PS_TYPE = 'Pre Test' ) 
		AND L.Location_ID = (SELECT Location_ID FROM Location WHERE VENDOR_ID = @FROM_VENDOR_ID AND  L_Type = 'Class Store')

	INSERT INTO @RESULTS ( BASEPRODUCT_NAME, FROM_PART_NUMBER, TO_PART_NUMBER, FROM_PROCESSSTEP_ID, TO_PROCESSSTEP_ID, FROM_VENDOR_ID, FROM_VENDOR_NAME, TO_VENDOR_ID, TO_VENDOR_NAME, PS_TYPE, PS_TYPE_ID, LOT_ID, LOT_NUMBER, FAB_LOT_NUMBER, QTY_AVAILABLE, DATE_IN, EXPECTED_DATE_OUT, QTY_PULLED ) 
		SELECT (select TOP 1 P.Part_number from assm_release_lot_Table arlt, Lot_Table LTT, Product p where LTT.Lot_Id = arlt.Lot_ID and LTT.ProcessStep_id = P.ProcessStep_ID and ARLT.Shipped_lot_number = ( CASE WHEN LEN(LT.Lot_number) > 5 THEN SUBSTRING( Lt.Lot_number, 0, 6 ) ELSE LT.Lot_Number END )), P.Part_Number, @RELEASE_PART_NUMBER, Lt.ProcessStep_ID, @RELEASE_PROCESSSTEP_ID, @FROM_VENDOR_ID, @FROM_VENDOR_NAME, @TO_VENDOR_ID, @RELEASE_VENDOR_NAME, PS.PS_Type, 1, Lt.Lot_ID, Lt.Lot_Number, Lt.Fab_Lot_number, Lt.Qty_In, 
		Lt.Date_In, Lt.Date_In, (SELECT SUM( ISNULL( QTY_PULLED, 0 ) ) FROM TEST_RELEASE_LOT_TABLE TRLT, TEST_RELEASE_TABLE TRT WHERE TRLT.TEST_REL_ID = TRT.TEST_REL_ID AND TEST_REL_STATUS <> 'CANCELLED' AND TRLT.LOT_ID = LT.LOT_ID )
		FROM Lot_Table LT, PRODUCT P, PROCESS_STEP PS, LOCATION L, Process_Parameter PP WHERE Lt.ProcessStep_ID = P.ProcessStep_ID AND Lt.ProcessStep_ID = PS.ProcessStep_ID AND Lt.Location_ID = L.Location_ID 
		AND Lt.ProcessStep_ID = PP.ProcessStep_ID
		AND LT.LOT_CURRENT_STATUS <> 'Close' AND Lt.ProcessStep_ID IN ( SELECT PROCESSSTEP_ID FROM @PROGRAMMABLE_PARTS_TABLE ) 
		AND L.Location_ID = (SELECT Location_ID FROM Location WHERE VENDOR_ID = @FROM_VENDOR_ID AND  L_Type = 'Finished Goods')
		AND PP.Parameter_Key = 'Is Programmable Part' AND PP.Parameter_Value = 'True'

	RETURN
END

